package ro.gateway.aida.obj.db;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.Person;
import ro.gateway.aida.obj.searchrows.PersonSelectorRow;
import ro.gateway.aida.servlet.Constants;
import ro.gateway.aida.srv.ICFService;
import ro.gateway.aida.srv.IIDGenerator;
import ro.gateway.aida.utils.IDString;
import ro.gateway.aida.utils.SortedList;


public class PersonDB extends DBPersistenceManager {
     private PersonDB( PersistenceToken token ) {
          super( token );
     }

     public static PersonDB getManager( PersistenceToken token ) {
          return new PersonDB( token );
     }

     public Vector getAll( long userID ) {
          Connection con = getConnection();
          Vector res = new Vector();

          try {
               PreparedStatement ps1 = con.prepareStatement( allListUser );
               ps1.setLong( 1, userID );

               ResultSet rs1 = ps1.executeQuery();

               while ( rs1.next() )
                    res.add( getByID( rs1.getLong( 1 ) ) );

               rs1.close();
               ps1.close();
          } catch ( SQLException sqlex ) {
               return null;
          } finally {
               try {
                    con.close();
               } catch ( SQLException sqlEx ) {
               }
          }

          return res;
     }

     public Vector getAll() throws SQLException {
          Connection con = getConnection();
          Vector res = new Vector();

          try {
               PreparedStatement ps1 = con.prepareStatement( allList );
               ResultSet rs1 = ps1.executeQuery();

               while ( rs1.next() )
                    res.add( getByID( rs1.getLong( 1 ) ) );

               rs1.close();
               ps1.close();
          } catch ( SQLException sqlex ) {
               throw sqlex;
          } finally {
               con.close();
          }

          return res;
     }

     public Vector getByLike( String like ) throws SQLException {
          Connection con = getConnection();
          Vector res = new Vector();
          //like = like.toUpperCase();

          try {
               PreparedStatement ps1 = con.prepareStatement( likeList );
               ps1.setString( 1, "%" + like + "%" );
               ps1.setString( 2, "%" + like + "%" );

               ResultSet rs1 = ps1.executeQuery();

               while ( rs1.next() )
                    res.add( getByID( rs1.getLong( 1 ) ) );

               rs1.close();
               ps1.close();
          } catch ( SQLException sqlex ) {
               throw sqlex;
          } finally {
               con.close();
          }

          return res;
     }

     public Vector getUnselected() throws SQLException {
          Connection con = getConnection();
          Vector res = new Vector();

          try {
               PreparedStatement ps1 = con.prepareStatement( unselectedList );
               ResultSet rs1 = ps1.executeQuery();

               while ( rs1.next() )
                    res.add( getByID( rs1.getLong( 1 ) ) );

               rs1.close();
               ps1.close();
          } catch ( SQLException sqlex ) {
               throw sqlex;
          } finally {
               con.close();
          }

          return res;
     }

     public Vector getByOrg( String orgs ) throws SQLException {
          Connection con = getConnection();
          Vector res = new Vector();

          try {
               PreparedStatement ps1 = con.prepareStatement( orgList + orgs + ")" );
               ResultSet rs1 = ps1.executeQuery();

               while ( rs1.next() )
                    res.add( getByID( rs1.getLong( 1 ) ) );

               rs1.close();
               ps1.close();
          } catch ( SQLException sqlex ) {
               throw sqlex;
          } finally {
               con.close();
          }

          return res;
     }

     public void update( Person pers ) throws SQLException {
          Connection con = getConnection();

          try {
               PreparedStatement ps = con.prepareStatement( updatePerson );
               ps.setString( 1, pers.getFirstNames() );
               ps.setString( 2, pers.getLastName() );
               ps.setString( 3, pers.getDepartment() );
               ps.setString( 4, pers.getState() );
               ps.setString( 5, pers.getAddress() );
               ps.setString( 6, pers.getPostalCode() );
               ps.setString( 7, pers.getRawPhone() );
               ps.setString( 8, pers.getRawFax() );
               ps.setString( 9, pers.getEmail() );
               ps.setString( 10, pers.getURL() );
               ps.setString( 11, pers.getCountryISO3() );
               ps.setDate( 12, new Date( System.currentTimeMillis() ) );
               ps.setLong( 13, pers.getId() );
               ps.executeUpdate();
               ps.close();
          } catch ( SQLException ex ) {
               ex.printStackTrace();
          } finally {
               con.close();
          }
     }

     public void insert( IIDGenerator idGenerators, Person pers )
        throws SQLException {
          Connection con = getConnection();

          try {
               pers.setId( idGenerators.nextVal( Constants.IDGEN_PERSONS ) );

               PreparedStatement ps = con.prepareStatement( insertPerson );
               ps.setString( 1, pers.getFirstNames() );
               ps.setString( 2, pers.getLastName() );
               ps.setString( 3, pers.getDepartment() );
               ps.setString( 4, pers.getState() );
               ps.setString( 5, pers.getAddress() );
               ps.setString( 6, pers.getPostalCode() );
               ps.setString( 7, pers.getRawPhone() );
               ps.setString( 8, pers.getRawFax() );
               ps.setString( 9, pers.getEmail() );
               ps.setString( 10, pers.getURL() );
               ps.setString( 11, pers.getCountryISO3() );
               ps.setLong( 12, pers.getId() );
               ps.setLong( 13, pers.getUserID() );
               ps.setDate( 14, new Date( System.currentTimeMillis() ) );
               ps.executeUpdate();
               ps.close();
          } catch ( SQLException ex ) {
               ex.printStackTrace();
          } finally {
               con.close();
          }
     }

     public String getCountryName( Person pers ) {
          Connection con = getConnection();
          String ret = "";

          try {
               PreparedStatement ps = con.prepareStatement( countryName );
               ps.setString( 1, pers.getCountryISO3() );

               ResultSet rs = ps.executeQuery();

               if ( rs.next() )
                    ret = rs.getString( 1 );

               rs.close();
               ps.close();
          } catch ( SQLException e ) {
               e.printStackTrace();
          } finally {
               try {
                    con.close();
               } catch ( SQLException sqlEx ) {
               }
          }

          return ret;
     }

     public String createCountryList( Person pers ) {
          StringBuffer b = new StringBuffer();
          Connection con = getConnection();

          try {
               PreparedStatement ps = con.prepareStatement( countryList );
               ResultSet rs = ps.executeQuery();

               while ( rs.next() ) {
                    String stat = ">";

                    if ( pers.getCountryISO3().equals( rs.getString( 2 ) ) )
                         stat = " selected>";

                    b.append( "<option value=" + rs.getString( 2 ) + stat +
                       rs.getString( 1 ) + "</option>" );
               }

               rs.close();
               ps.close();
          } catch ( SQLException e ) {
               e.printStackTrace();
          } finally {
               try {
                    con.close();
               } catch ( SQLException sqlEx ) {
               }
          }

          return b.toString();
     }

     public void delete( long persID ) throws SQLException {
          Connection con = getConnection();

          try {
               PreparedStatement ps2 = con.prepareStatement( deletePerson );
               ps2.setLong( 1, persID );
               ps2.executeUpdate();
               ps2.close();
          } catch ( SQLException ex ) {
               throw ex;
          } finally {
               con.close();
          }
     }
     
     public List getOrgsForPerson( long persID, String lang ) throws SQLException {
         Connection con = getConnection();
         ArrayList ret=new ArrayList();
         
         try {
              PreparedStatement ps2 = con.prepareStatement( ORGS_FOR_PERSON );
              ps2.setString(1, lang);
              ps2.setLong(2, persID);
              ResultSet rs = ps2.executeQuery();
              if ( rs.next() ) {
                  IDString s=new IDString();
                  s.setId(rs.getLong(1));
                  s.setText(rs.getString(2));
                  ret.add(s);
              }
              ps2.close();
         } catch ( SQLException ex ) {
              throw ex;
         } finally {
              con.close();
         }
         return ret;
    }
     
     public Person getByID( long id) throws SQLException {
         return getByID(id,Constants.DEFAULT_LANGUAGE);
     }

     public Person getByID( long id, String lang ) throws SQLException {
          Connection con = getConnection();
          Person pers = null;

          try {
               PreparedStatement ps = con.prepareStatement( getPerson );
               ps.setLong( 1, id );

               ResultSet rs = ps.executeQuery();

               if ( rs.next() ) {
                    pers = new Person();
                    pers.setId( id );
                    pers.setFirstNames( rs.getString( 1 ) );
                    pers.setLastName( rs.getString( 2 ) );
                    pers.setDepartment( rs.getString( 3 ) );
                    pers.setState( rs.getString( 4 ) );
                    pers.setAddress( rs.getString( 5 ) );
                    pers.setPostalCode( rs.getString( 6 ) );
                    pers.setPhone( rs.getString( 7 ) );
                    pers.setFax( rs.getString( 8 ) );
                    pers.setEmail( rs.getString( 9 ) );
                    pers.setURL( rs.getString( 10 ) );
                    pers.setCountryISO3( rs.getString( 11 ) );
                    pers.setUserID( rs.getLong( 12 ) );
                    pers.setCountryName( rs.getString( 13 ) );
                    pers.setUpdate( rs.getDate( 14 ) );
               }

               rs.close();
               ps.close();
          } catch ( SQLException sqlEx ) {
               throw sqlEx;
          } finally {
               try {
                    con.close();
               } catch ( SQLException sqlEx ) {
               }
          }
          
          //attach org names:
          pers.setAssignedToOrgs(this.getOrgsForPerson(id, lang));
          
          return pers;
     }

     
     
     
     
     public SortedList selectorSearch( String name, String keyword, String lang )
        throws SQLException {
          Connection con = getConnection();
          SortedList sl = new SortedList();
          name = name.trim();
          keyword = keyword.trim();
          try {
               PreparedStatement ps = con.prepareStatement( SELECTOR_SEARCH );
               ps.setString( 1, lang );
               ps.setString( 2, "%" + name + "%" );
               ps.setString( 3, "%" + name + "%" );
               ps.setString( 4, "%" + keyword + "%" );
               ps.setString( 5, "%" + keyword + "%" );

               ResultSet rs = ps.executeQuery();

               while ( rs.next() ) {
                    PersonSelectorRow psr = new PersonSelectorRow();
                    psr.setId( rs.getLong( 1 ) );
                    psr.setFirstNames( rs.getString( 2 ) );
                    psr.setLastName(rs.getString(3));
                    psr.setOrgName( rs.getString( 4 ) );
                    psr.setOrgId( rs.getLong( 5 ) );
                    sl.add( psr );
               }
          } finally {
               con.close();
          }

          return sl;
     }

     public SortedList getList() throws SQLException {
          Connection con = getConnection();
          Person pers = null;
          SortedList sl = new SortedList();

          try {
               PreparedStatement ps = con.prepareStatement( PERSONS_LIST );
               ResultSet rs = ps.executeQuery();

               while ( rs.next() ) {
                    pers = new Person();
                    pers.setId( rs.getLong( 15 ) );
                    pers.setFirstNames( rs.getString( 1 ) );
                    pers.setLastName( rs.getString( 2 ) );
                    pers.setDepartment( rs.getString( 3 ) );
                    pers.setState( rs.getString( 4 ) );
                    pers.setAddress( rs.getString( 5 ) );
                    pers.setPostalCode( rs.getString( 6 ) );
                    pers.setPhone( rs.getString( 7 ) );
                    pers.setFax( rs.getString( 8 ) );
                    pers.setEmail( rs.getString( 9 ) );
                    pers.setURL( rs.getString( 10 ) );
                    pers.setCountryISO3( rs.getString( 11 ) );
                    pers.setUserID( rs.getLong( 12 ) );
                    pers.setCountryName( rs.getString( 13 ) );
                    pers.setUpdate( rs.getDate( 14 ) );
                    sl.add( pers );
               }

               rs.close();
               ps.close();
          } finally {
               con.close();
          }

          return sl;
     }

     public static long getLastId( ICFService cfService, String poolName )
        throws SQLException {
          long result = 0;
          Connection con = cfService.getConnection( poolName );

          try {
               PreparedStatement ps = con.prepareStatement( GET_MAX_ID );
               ResultSet rs = ps.executeQuery();
               if ( rs.next() ) {
                    result = rs.getLong( 1 );
               }
               rs.close();
               ps.close();
          } catch ( SQLException sqlEx ) {
               throw sqlEx;
          } finally {
               con.close();
          }

          return result;
     }

     private static final String GET_MAX_ID =
        "SELECT max(id) FROM persons";

     private static final String SELECTOR_SEARCH =
        "SELECT p2.id, p2.first_names,p2.last_name,lpd_get_org_name(op2.org_id,?),op2.org_id FROM persons p2" +
        " LEFT OUTER JOIN org_pers op2 ON (op2.pers_id=p2.id) WHERE p2.id IN (" +
        "SELECT id FROM persons WHERE UPPER(first_names) ILIKE(?) OR last_name ILIKE(?) UNION " +
        "SELECT p.id FROM persons p, org_pers op, orgs_desc od WHERE p.id=op.pers_id AND" +
        " op.org_id=od.id AND (od.name ILIKE(?) OR p.addr1 ILIKE(?) ) )";
     private static final String allList = "select id from persons";
     private static final String allListUser = "select id from persons where user_id=? ORDER BY updated";
     private static final String deletePerson = "delete from persons where id=?";
     private static final String unselectedList = "select id from persons where id not in (select distinct person from orgs)";
     
     private static final String ORGS_FOR_PERSON = "SELECT DISTINCT org_id, lpd_get_org_name(org_id,?) FROM org_pers WHERE pers_id=?";
     private static final String orgList = "select distinct pers_id from org_pers where org_id in(";
     private static final String likeList = "select distinct id from persons where first_names ILIKE(?) or last_name ILIKE(?)";
     private static final String updatePerson =
        "update persons set first_names=?, last_name=?,departament=?" +
        ",state=?,addr1=?,postal_code=?,phone=?,fax=?,email=?,url=?,country_code=?,updated=? " +
        "where id=?";
     private static final String insertPerson =
        "insert into persons (first_names,last_name,departament,state,addr1,postal_code" +
        ",phone,fax,email,url,country_code,id,user_id,updated)" +
        " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
     private static final String getPerson =
        "select p.first_names,p.last_name,p.departament,p.state,p.addr1,p.postal_code" +
        ",p.phone,p.fax,p.email,p.url,p.country_code,p.user_id,c.name,p.updated from persons p , countries_iso3 c where c.iso3=p.country_code and id=?";
     private static final String PERSONS_LIST =
        "select p.first_names,p.last_name,p.departament,p.state,p.addr1,p.postal_code" +
        ",p.phone,p.fax,p.email,p.url,p.country_code,p.user_id,c.name,p.updated,p.id from persons p , countries_iso3 c where c.iso3=p.country_code";
     private static final String countryList = "select name,iso3 from countries_iso3 order by name";
     private static final String countryName = "select name from countries_iso3 where iso3=?";
}
